#!/usr/bin/env python

## Copyright 2014 Francesco Bailo

## This program is free software: you can redistribute it and/or modify
## it under the terms of the GNU General Public License as published by
## the Free Software Foundation, either version 3 of the License, or
## (at your option) any later version.

## This program is distributed in the hope that it will be useful,
## but WITHOUT ANY WARRANTY; without even the implied warranty of
## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
## GNU General Public License for more details.

## You should have received a copy of the GNU General Public License
## along with this program.  If not, see <http://www.gnu.org/licenses/>.

# createDb.py

# Call it from console passing database filename: python createdb.py filename

import sqlite3
import sys

# Receive the argument from bash
filename = sys.argv[1] + '.sqlite'
 
conn = sqlite3.connect(filename)
 
cursor = conn.cursor()
 
# Create database

# Create tables

cursor.execute("""
CREATE TABLE petition (
    petition_id TEXT PRIMARY KEY,
    title TEXT,
    status TEXT,
    url TEXT,
    overview TEXT,
    letter_body TEXT,
    signature_count INTEGER,
    image_url TEXT,
    category TEXT,
    goal INT,
    created_at DATETIME,
    end_at DATETIME,
    creator_name TEXT,
    creator_url TEXT,
    organization_name TEXT,
    organization_url CHAR,
    timestamp DATETIME DEFAULT ( CURRENT_TIMESTAMP )
    );
               """)

cursor.execute("""
CREATE TABLE target (
    petition_id TEXT,
    name TEXT,
    title TEXT,
    type TEXT,
    target_area TEXT,
    timestamp DATETIME DEFAULT ( CURRENT_TIMESTAMP ),
    FOREIGN KEY(petition_id) REFERENCES petition(petition_id)
    );
               """)

cursor.execute("""
CREATE TABLE signature (
    petition_id TEXT,
    name TEXT,
    city TEXT,
    state_province TEXT,
    country_name TEXT,
    country_code TEXT,
    signed_at DATETIME,
    timestamp DATETIME DEFAULT ( CURRENT_TIMESTAMP ),
    FOREIGN KEY(petition_id) REFERENCES petition(petition_id)
    );
               """)

cursor.execute("""
CREATE TABLE reason (
    petition_id TEXT,
    created_at DATETIME,
    content TEXT,
    like_count INTEGER,
    author_name TEXT,
    author_url TEXT,
    timestamp DATETIME DEFAULT ( CURRENT_TIMESTAMP ),
    FOREIGN KEY(petition_id) REFERENCES petition(petition_id)
    );
               """)

cursor.execute("""
CREATE TABLE user (
    user_id TEXT PRIMARY KEY,
    name TEXT,
    location TEXT,
    city TEXT,
    state_province TEXT,
    country_name TEXT,
    countr_code TEXT,
    timestamp DATETIME DEFAULT ( CURRENT_TIMESTAMP )
    );
               """)

cursor.execute("""
CREATE TABLE organization (
    organization_id TEXT PRIMARY KEY,
    name TEXT,
    location TEXT,
    address TEXT,
    city TEXT,
    state_province TEXT,
    postal_code TEXT,
    country_name TEXT,
    countr_code TEXT,
    website TEXT,
    mission TEXT,
    organization_url,
    timestamp DATETIME DEFAULT ( CURRENT_TIMESTAMP )
    );
               """)


# Close connection
conn.commit()
